"""数据库配置优化模块

包含数据库连接、性能优化和缓存策略的配置。
"""

from decouple import config
import os

# 数据库连接配置
DATABASE_CONFIG = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': config('DB_NAME', default='linkbook'),
        'USER': config('DB_USER', default='postgres'),
        'PASSWORD': config('DB_PASSWORD', default='password'),
        'HOST': config('DB_HOST', default='localhost'),
        'PORT': config('DB_PORT', default='5432'),
        'OPTIONS': {
            # 连接选项
            'sslmode': config('DB_SSL_MODE', default='prefer'),
            'connect_timeout': 10,
            'options': '-c default_transaction_isolation=read_committed'
        },
        # 连接池配置
        'CONN_MAX_AGE': config('DB_CONN_MAX_AGE', default=600, cast=int),  # 10分钟
        'CONN_HEALTH_CHECKS': True,
        # 性能优化
        'ATOMIC_REQUESTS': False,  # 避免不必要的事务开销
        'AUTOCOMMIT': True,
    }
}

# 生产环境数据库优化配置
PRODUCTION_DATABASE_CONFIG = {
    'default': {
        **DATABASE_CONFIG['default'],
        'OPTIONS': {
            **DATABASE_CONFIG['default']['OPTIONS'],
            # 生产环境SSL配置
            'sslmode': 'require',
            # 连接池优化
            'MAX_CONNS': config('DB_MAX_CONNS', default=20, cast=int),
            'MIN_CONNS': config('DB_MIN_CONNS', default=5, cast=int),
            # 性能调优
            'application_name': 'linkbook_prod',
            'statement_timeout': 30000,  # 30秒查询超时
            'lock_timeout': 10000,       # 10秒锁超时
        },
        # 生产环境连接池
        'CONN_MAX_AGE': 3600,  # 1小时
    }
}

# 开发环境数据库配置
DEVELOPMENT_DATABASE_CONFIG = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'db.sqlite3'),
        'OPTIONS': {
            # SQLite优化
            'timeout': 20,
            'check_same_thread': False,
        },
        'CONN_MAX_AGE': 0,  # 开发环境不使用连接池
    }
}

# 数据库路由配置
class DatabaseRouter:
    """数据库路由器
    
    用于多数据库环境下的读写分离。
    """
    
    def db_for_read(self, model, **hints):
        """读操作路由"""
        # 可以配置读写分离
        return 'default'
    
    def db_for_write(self, model, **hints):
        """写操作路由"""
        return 'default'
    
    def allow_relation(self, obj1, obj2, **hints):
        """允许关联查询"""
        return True
    
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """允许迁移"""
        return True

# 查询优化配置
QUERY_OPTIMIZATION = {
    # 查询集缓存
    'QUERYSET_CACHE_TIMEOUT': 300,  # 5分钟
    
    # 分页配置
    'DEFAULT_PAGE_SIZE': 20,
    'MAX_PAGE_SIZE': 100,
    
    # 预加载配置
    'DEFAULT_SELECT_RELATED': ['author', 'category'],
    'DEFAULT_PREFETCH_RELATED': ['tags', 'attachments'],
    
    # 批量操作配置
    'BULK_CREATE_BATCH_SIZE': 1000,
    'BULK_UPDATE_BATCH_SIZE': 1000,
}

# 索引优化建议
INDEX_RECOMMENDATIONS = {
    'notes_note': [
        'CREATE INDEX CONCURRENTLY idx_notes_author_status ON notes_note(author_id, status);',
        'CREATE INDEX CONCURRENTLY idx_notes_created_at_desc ON notes_note(created_at DESC);',
        'CREATE INDEX CONCURRENTLY idx_notes_updated_at_desc ON notes_note(updated_at DESC);',
        'CREATE INDEX CONCURRENTLY idx_notes_view_count_desc ON notes_note(view_count DESC);',
        'CREATE INDEX CONCURRENTLY idx_notes_title_gin ON notes_note USING gin(to_tsvector(\'english\', title));',
        'CREATE INDEX CONCURRENTLY idx_notes_content_gin ON notes_note USING gin(to_tsvector(\'english\', content));',
    ],
    'authentication_user': [
        'CREATE INDEX CONCURRENTLY idx_auth_user_email ON auth_user(email);',
        'CREATE INDEX CONCURRENTLY idx_auth_user_last_activity ON auth_user(last_activity DESC);',
    ],
    'note_category': [
        'CREATE INDEX CONCURRENTLY idx_category_parent_order ON note_category(parent_id, "order");',
    ],
    'note_tag': [
        'CREATE INDEX CONCURRENTLY idx_tag_usage_count_desc ON note_tag(usage_count DESC);',
    ],
}

# 数据库维护配置
DATABASE_MAINTENANCE = {
    # 自动清理配置
    'AUTO_VACUUM': True,
    'VACUUM_SCHEDULE': '0 2 * * 0',  # 每周日凌晨2点
    
    # 统计信息更新
    'ANALYZE_SCHEDULE': '0 3 * * *',  # 每天凌晨3点
    
    # 日志清理
    'LOG_RETENTION_DAYS': 30,
    'LOG_CLEANUP_SCHEDULE': '0 4 * * *',  # 每天凌晨4点
    
    # 备份配置
    'BACKUP_SCHEDULE': '0 1 * * *',  # 每天凌晨1点
    'BACKUP_RETENTION_DAYS': 7,
}

# 监控和告警配置
DATABASE_MONITORING = {
    # 性能监控
    'SLOW_QUERY_THRESHOLD': 1.0,  # 1秒
    'CONNECTION_POOL_ALERT_THRESHOLD': 0.8,  # 80%使用率告警
    
    # 磁盘空间监控
    'DISK_USAGE_ALERT_THRESHOLD': 0.85,  # 85%使用率告警
    
    # 连接数监控
    'MAX_CONNECTIONS_ALERT_THRESHOLD': 0.9,  # 90%连接数告警
}